﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Data.Linq.SqlClient;
using System.Text;
using System.Transactions;

namespace InSys.Documents
{
    public struct ReportParams
    {
        public string name; // фио сторудника
        public int value;   // количественное значение
    }

    public class ClassWorkWithDB
    {
        ///// <summary>
        ///// Получить список сотрудников отдела на указанную дату
        ///// </summary>
        ///// <param name="db"></param>
        ///// <param name="id_department"></param>
        ///// <param name="actual_data"></param>
        ///// <returns></returns>
        //public static List<person> getListPersonsDepartment(MapTestLinqDataContext db, int id_department, DateTime actual_date)
        //{
        //    actual_date = new DateTime(actual_date.Year, actual_date.Month, actual_date.Day, 23, 59, 59);
        //    var queryPersons = from item in db.person_departments group item by new { name = item.person.name, id = item.id_person } into itemGroup orderby itemGroup.Key.name select itemGroup;
        //    List<person> Person = new List<person>();
        //    foreach (var person in queryPersons)
        //    {
        //        person_department val = person.Where(x => x.date <= actual_date).OrderByDescending(x => x.date).First();
        //        if (val.department.id_department == id_department)
        //            Person.Add(val.person);
        //    }
        //    return Person.OrderBy(o=>o.name).ToList();
        //}

        /// <summary>
        /// Получить список идентификаторов документов, находящихся в данном отделе в данную дату
        /// </summary>
        /// <param name="db"></param>
        /// <param name="id_person"></param>
        /// <param name="actual_date"></param>
        /// <returns></returns>

        public static IEnumerable<document> getListDocument(MapTestLinqDataContext db, int id_person, DateTime actual_date, int kind_document)
        {
            actual_date = new DateTime(actual_date.Year, actual_date.Month, actual_date.Day, 23, 59, 59);

            var d = (from b in db.person_documents where b.datetime <= actual_date group b by b.id_document into grp select new { id_document = grp.Key, last_date = grp.Max(o => o.datetime) });
            var sel_doc = from a in db.person_documents.Where(o => o.id_person == id_person) from b in d where a.id_document == b.id_document && a.datetime == b.last_date orderby b.last_date descending select a.id_document;

            var query = from a in db.document from sel in sel_doc where a.id_kindDocument == kind_document & a.id_document == sel select a;
            return query;
        }


        /// <summary>
        /// Передать документ другому сотруднику
        /// </summary>
        /// <param name="db"></param>
        /// <param name="id_document"></param>
        /// <param name="id_person"></param>
        /// <param name="actual_date"></param>
        public static void TransferDocument(MapTestLinqDataContext db, int id_document, int id_person, DateTime actual_date, string comment)
        {
            person_document CurrentTransferDocument = new person_document(); ;
            var QueryCurrentPerson = from item in db.persons where item.id_person == id_person select item;
            person CurrentPerson = QueryCurrentPerson.First();
            
            CurrentTransferDocument.datetime = DateTime.Now;
            CurrentTransferDocument.id_person = id_person;
            CurrentTransferDocument.id_document = id_document;
            CurrentTransferDocument.description = comment;
            db.person_documents.InsertOnSubmit(CurrentTransferDocument);
            db.SubmitChanges();
        }


        /// <summary>
        /// Получить список идентификаторов документов, находящихся в данном отделе в данную дату
        /// </summary>
        /// <param name="db"></param>
        /// <param name="id_person"></param>
        /// <param name="actual_date"></param>
        /// <returns></returns>
        public static IEnumerable<document> getListDocument(MapTestLinqDataContext db, DateTime actual_date, FormSearchDocument srch_frm, int kind_document)
        {
            bool isEmptyRegisterNumber = srch_frm.RegistryNumber.Trim().Length == 0;
            bool isEmptyEnterprise = srch_frm.Enterprise.Trim().Length == 0;
            bool isEmptyDescription = srch_frm.Content.Trim().Length == 0;
            bool isEmptyLocalID = srch_frm.LocalID.Trim().Length == 0;
            bool isEmptyInternalNumber = srch_frm.InternalNumber == 0;
            bool isEmptyAddress = srch_frm.Address.Trim().Length == 0;

            actual_date = new DateTime(actual_date.Year, actual_date.Month, actual_date.Day, 23, 59, 59);

            var d = (from b in db.person_documents where b.datetime <= actual_date group b by b.id_document into grp select new { id_document = grp.Key, last_date = grp.Max(o => o.datetime) });
            var  sel_doc = from a in db.person_documents.Where(o =>srch_frm.NotUsed | o.id_person == srch_frm.UserID) from b in d where a.id_document == b.id_document && a.datetime == b.last_date orderby b.last_date descending select a.id_document;
            var query = from a in db.document from sel in sel_doc where 
                            a.id_document == sel &
                            (isEmptyEnterprise || a.enterprise.ToUpper().Contains(srch_frm.Enterprise.Trim().ToUpper())) &&
                            (isEmptyDescription || a.description.ToUpper().Contains(srch_frm.Content.Trim().ToUpper())) &&
                            (isEmptyLocalID || a.local_id.ToUpper().Contains(srch_frm.LocalID.ToUpper())) &&
                            (isEmptyInternalNumber || (a.id_document == srch_frm.InternalNumber)) &&
                            (a.id_kindDocument == Program.gKindDocument) &&
                            (isEmptyAddress || a.address.ToUpper().Contains(srch_frm.Address.Trim().ToUpper())) 

            select a;

            return query; 
        }
        /// <summary>
        /// Получить список идентификаторов документов, находящихся в данном отделе в данную дату
        /// </summary>
        /// <param name="db"></param>
        /// <param name="id_person"></param>
        /// <param name="actual_date"></param>
        /// <returns></returns>
        public static IEnumerable<document> getListDocument(MapTestLinqDataContext db, DateTime actual_date, UCSearchDocument srch_frm)
        {
            bool isEmptyRegisterNumber = srch_frm.RegistryNumber.Trim().Length == 0;
            bool isEmptyEnterprise = srch_frm.Enterprise.Trim().Length == 0;
            bool isEmptyDescription = srch_frm.Content.Trim().Length == 0;
            bool isEmptyLocalID = srch_frm.LocalID.Trim().Length == 0;
            bool isEmptyInternalNumber = srch_frm.InternalNumber == 0;
            bool isEmptyAddress;
            bool isEmptyExtRegNumber = srch_frm.ExtRegistryNumber.Trim().Length == 0;
            bool isEmptyExtRegDate = srch_frm.ExtRegistryDate == new DateTime(1899, 12, 30);
            if (srch_frm.Address == null)
                isEmptyAddress = true;
            else
                isEmptyAddress = srch_frm.Address.Trim().Length == 0;

            actual_date = new DateTime(actual_date.Year, actual_date.Month, actual_date.Day, 23, 59, 59);

            var d = (from b in db.person_documents where b.datetime <= actual_date group b by b.id_document into grp select new { id_document = grp.Key, last_date = grp.Max(o => o.datetime) });
            var sel_doc = from a in db.person_documents.Where(o => srch_frm.NotUsed | o.id_person == srch_frm.UserID) from b in d where a.id_document == b.id_document && a.datetime == b.last_date orderby b.last_date descending select a.id_document;
            var query = from a in db.document
                        from sel in sel_doc
                        where
                            a.id_document == sel &
                            (isEmptyEnterprise || a.enterprise.ToUpper().Contains(srch_frm.Enterprise.Trim().ToUpper())) &&
                            (isEmptyDescription || a.description.ToUpper().Contains(srch_frm.Content.Trim().ToUpper())) &&
                            (isEmptyLocalID || a.local_id.ToUpper().Contains(srch_frm.LocalID.ToUpper())) &&
                            (isEmptyInternalNumber || (a.id_document == srch_frm.InternalNumber)) &&
                            (srch_frm.NotUsedKindDocument || a.id_kindDocument == srch_frm.KindDocumentID) &&
                            (isEmptyAddress || a.address.ToUpper().Contains(srch_frm.Address.Trim().ToUpper())) 
                        select a;

            return query;
        }

        /// <summary>
        /// Получить список родительских документов
        /// </summary>
        /// <param name="db"></param>
        /// <param name="currentDocument"></param>
        /// <returns></returns>
        public static IEnumerable<document> getParentDocuments(MapTestLinqDataContext db, document currentDocument)
        {
            IEnumerable<document> result = null;
            result = from item in db.document orderby item.date from lnk in db.parentDocumentLink where item.id_document == lnk.id_parent_document && lnk.id_child_document == currentDocument.id_document select item;
            return result;
        }

        /// <summary>
        /// Получить список дочерних документов
        /// </summary>
        /// <param name="db"></param>
        /// <param name="currentDocument"></param>
        /// <returns></returns>
        public static IEnumerable<document> getChildDocuments(MapTestLinqDataContext db, document currentDocument)
        {
            IEnumerable<document> result = null;
            result = from item in db.document orderby item.date from lnk in db.parentDocumentLink where item.id_document == lnk.id_child_document && lnk.id_parent_document == currentDocument.id_document select item;
            return result;
        }

        public static person_document getPersonDocumentByID(MapTestLinqDataContext db, int id)
        {
            person_document result = (from item in db.person_documents where item.id_operation == id select item).FirstOrDefault() as person_document;
            return result;
        }

        public static void DeleteDocument(MapTestLinqDataContext db, int id_document)
        {
            using (TransactionScope ts = new TransactionScope())
            {
                var alarm_objects = from item_alarm in db.Alarm where item_alarm.id_document == id_document select item_alarm;
                var hierarchy_object = from item_hier in db.parentDocumentLink where item_hier.id_child_document == id_document || item_hier.id_parent_document == id_document select item_hier;
                var movement_operations = from item_mvmnt in db.person_documents where item_mvmnt.id_document == id_document select item_mvmnt;
                var doc = from item_doc in db.document where item_doc.id_document == id_document select item_doc;
                foreach (Alarm item in alarm_objects)
                {
                    db.Alarm.DeleteOnSubmit(item);
                }
                foreach (parentDocumentLink item in hierarchy_object)
                {
                    db.parentDocumentLink.DeleteOnSubmit(item);
                }
                foreach (person_document item in movement_operations)
                {
                    db.person_documents.DeleteOnSubmit(item);
                }
                db.SubmitChanges();
                foreach (document item in doc)
                {
                    db.document.DeleteOnSubmit(item);
                }
                db.SubmitChanges();
                ts.Complete();
            }
        }

   }
}
